package cn.biplam.back.operator.ddl.db2sql.dictionary;

import java.util.ArrayList;

import cn.biplam.common.client.DataFilter;
import cn.biplam.common.tools.TOOLS;

/**
 * 获取oracle数据库数据字典数据操作类
 * <ul> 功能：
 * <li> 获取表信息
 * <li> 获取表字段信息
 * <li> 获取外键信息
 * <li> 获取主键信息
 * <li> 获取索引信息
 * </ul>
 * <ul> 需要传入参数：
 * <li> schema 用户
 * <li> table_name 表名       (可选)
 * <li> table_pattern 表名范围 (可选)
 * </ul>
 * @author XiaoRuxing 2009-6-25下午06:42:23
 * @version 1.0
 */
public class DictionaryOracle implements Dictionary {
	
	/** 用户 */
	private String sSchema=null;
	/** 表名 */
	private String sTableName=null;
	/** 表名适配器 */
	private String tableNamePattern=null;
	
	/**
	 * 取表信息
	 * <ul> 需要传入参数：
	 * <li> schema 用户
	 * <li> table_name 表名        
	 * <li> table_pattern 表名范围  　
	 * </ul>
	 */
	public void getTable(ArrayList<String> sql,DataFilter filter) {
		_init(filter);
		StringBuilder sb = new StringBuilder();
		sb.append(" select t.table_name as TABLE_NAME,tc.comments as TABLE_COMMENTS ");
		sb.append("   from dba_tables t,                               ");
		sb.append("        dba_tab_comments tc                         ");
		sb.append("  where t.table_name = tc.table_name                ");
		sb.append("    and tc.owner = t.owner                          ");
		if(sSchema!=null){
			sb.append("    and t.owner = '"+sSchema+"'                ");
		}
		if(sTableName!=null){
			sb.append("    and t.table_name = '"+sTableName+"'        ");
		}
		if(tableNamePattern!=null){
			sb.append("    and t.table_name like '%"+tableNamePattern+"%'");
		}
		sql.add(sb.toString());
	}

	/**
	 * 取表列信息
	 * 　 <ul> 需要传入参数：
	 * <li> schema 用户
	 * <li> table_name 表名  
	 */
	public void getColumns(ArrayList<String> sql,DataFilter filter) {
		_init(filter);
		StringBuilder sb = new StringBuilder();
		sb.append("  select utc.column_name as COLUMN_NAME,utc.data_type as TYPE_NAME,                           ");
		sb.append("         decode(utc.data_precision,null,utc.data_length,utc.data_precision) as COLUMN_SIZE,   ");
		sb.append("         utc.data_Scale as DECIMAL_DIGITS,utc.nullable as NULLABLE,                           ");
		sb.append("         utc.data_default as COLUMN_DEF,ucc.comments as REMARKS,                              ");
		sb.append("         utc.table_name as TABLE_NAME ,tc.comments as TABLE_COMMENTS                          ");
		sb.append("    from dba_tab_columns utc,                                                                 ");
		sb.append("         dba_tab_comments tc,                                                                 ");
		sb.append("         dba_col_comments ucc                                                                 ");
		sb.append("  where  utc.table_name = ucc.table_name                                                      ");
		sb.append("      and utc.column_name = ucc.column_name                                                   ");
		sb.append("      and utc.table_name = '"+sTableName+"'                                              ");
		sb.append("      and utc.OWNER = '"+sSchema+"'                                                      ");
		sb.append("      and utc.OWNER = ucc.owner                                                               ");
		sb.append("      and tc.owner = utc.OWNER                                                                ");
		sb.append("      and tc.table_name = utc.TABLE_NAME       ");
		sb.append("    order by utc.COLUMN_ID ");
		sql.add(sb.toString());
	}


	/** 
	 * 取主键信息
	 * 　 <ul> 需要传入参数：
	 * <li> schema 用户
	 * <li> table_name 表名  
	 */
	public void getPK(ArrayList<String> sql,DataFilter filter) {
		_init(filter);
		StringBuilder sb = new StringBuilder();
		sb.append(" select  tt.column_name as COLUMN_NAME,t.constraint_name as PK_NAME ");
		sb.append("   from dba_constraints t, dba_ind_columns tt  ");
		sb.append("  where t.table_name = '"+sTableName+"'   ");
		sb.append("    and tt.table_name=t.table_name          	  ");
		sb.append("    and t.constraint_type='P'              	  ");
		sb.append("    and t.index_name = tt.index_name       		");
		sb.append("    and t.owner = '"+sSchema+"'        		");
		sb.append("    and t.owner = tt.table_owner");
		sql.add(sb.toString());
	}

	/** 
	 * 取外键信息
	 * 　 <ul> 需要传入参数：
	 * <li> schema 用户
	 * <li> table_name 表名  
	 */
	public void getFK(ArrayList<String> sql,DataFilter filter) {
		_init(filter);
		StringBuilder sb = new StringBuilder();
		sb.append(" select pk.table_name as PKTABLE_NAME,             ");
		sb.append("        pkc.column_name as PKCOLUMN_NAME,          ");
		sb.append("        fk.table_name  as FKTABLE_NAME,            ");
		sb.append("        fk.constraint_name as FK_NAME,             ");
		sb.append("        fk.r_constraint_name as PK_NAME,            ");
		sb.append("        fkc.position as KEY_SEQ,            ");
		sb.append("        fkc.column_name as FKCOLUMN_NAME           ");
		sb.append("   from dba_constraints pk,                        ");
		sb.append("        dba_cons_columns pkc,                      ");
		sb.append("        dba_constraints fk,                        ");
		sb.append("        dba_cons_columns fkc                       ");
		sb.append("  where fk.constraint_type='R'                     ");
		sb.append("    and fk.r_constraint_name=pk.constraint_name    ");
		sb.append("    and fk.table_name = '"+sTableName+"'      ");
		sb.append("    and fk.owner = '"+sSchema+"'              ");
		sb.append("    and pk.constraint_name = pkc.constraint_name   ");
		sb.append("    and fk.constraint_name = fkc.constraint_name   ");
		sb.append("    and fk.owner = pk.owner   ");
		sb.append("    and fk.owner = fkc.owner  ");
		sb.append("    and pk.owner = pkc.owner   ");
		sb.append(" order by pk.table_name,fk.constraint_name ");
		sql.add(sb.toString());
	}

	/** 
	 * 取索引信息//数据字典查询语句需要确定(暂时不需要)
	 * 　 <ul> 需要传入参数：
	 * <li> schema 用户
	 * <li> table_name 表名  
	 */
	public void getIndexs(ArrayList<String> sql,DataFilter filter) {
		_init(filter);
		StringBuilder sb = new StringBuilder();
		sb.append(" select  tt.column_name as COLUMN_NAME,t.index_name as INDEX_NAME ,");
		sb.append(" t.constraint_type TYPE  ");
		sb.append("   from dba_constraints t,                         ");
		sb.append("        dba_ind_columns tt                         ");
		sb.append("   where t.table_name = '"+sTableName+"'      ");
		sb.append("       and tt.table_name=t.table_name              ");
		sb.append("       and t.constraint_type!='P'                  ");
		sb.append("       and t.index_name = tt.index_name            ");
		sb.append("       and t.owner = '"+sSchema+"'            ");
		sb.append("    and t.owner = tt.table_owner");
		sql.add(sb.toString());
	}
	
	/**
	 * 将参数转换成大小 
	 * @param filter
	 */
	private void _init(DataFilter filter){
		String sSchema = filter.getValue("schema");
		String sTableName = filter.getValue("table_name");
		String tableNamePattern = filter.getValue("table_pattern");
		if(!TOOLS.StringIsEmpty(sSchema)){
			this.sSchema = sSchema.toUpperCase();
		}
		if(!TOOLS.StringIsEmpty(sTableName)){
			this.sTableName = sTableName.toUpperCase();
		}
		if(!TOOLS.StringIsEmpty(tableNamePattern)){
			this.tableNamePattern = tableNamePattern.toUpperCase();
		}
		
	}
}
